/***********************************************************************************************************************************
 * Name:	Bereft_Logins.sql
 * Author:	Frank Figearo (frank@sqlnerd.me)
 * Summary:	Identify logins without any database user nor server role membership.
**/
USE master;
GO

IF OBJECT_ID(N'TempDB..#Logins') IS NOT NULL DROP TABLE #Logins;
CREATE TABLE #Logins (Name NVARCHAR(128) NOT NULL, SID VARBINARY(85) NOT NULL, Count INT NOT NULL DEFAULT (0));
INSERT INTO #Logins (Name, SID)
SELECT  sp.name, sp.sid
FROM    sys.server_principals sp
        LEFT JOIN sys.server_role_members srm ON (sp.principal_id = srm.member_principal_id)
WHERE   srm.member_principal_id IS NULL
        AND sp.type IN ('G', 'S', 'U')
        AND sp.name NOT IN ('sa', 'guest', 'DBOwner', 'JobOwner')
        AND sp.name NOT LIKE '##%##'
        AND sp.name NOT LIKE '%\%$'
        AND sp.name NOT LIKE '%\SQLServer%'
        AND sp.name NOT LIKE 'BUILTIN%'
        AND sp.name NOT LIKE 'NT AUTHORITY%'
        AND sp.name NOT LIKE 'NT SERVICE\%';

DECLARE @tsql NVARCHAR(MAX); SET @tsql= N'';
SELECT @tsql= @tsql + N'USE [' + database_name + N']; UPDATE #Logins SET Count= Count+1 FROM #Logins l INNER JOIN sys.database_principals dp ON (l.SID = dp.sid); ' FROM DBAdmin.DBList(N'@') dl
EXECUTE (@tsql);
USE master;

SELECT Name, TSQL= N'DROP LOGIN [' + Name + N'];' FROM #Logins WHERE Count = 0 ORDER BY Name;
DROP TABLE #Logins;
GO